/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package cafe.Model;

import cafe.DataObject.Customer;
import cafe.DataObject.DataObject;
import cafe.Model.Utility.DBConnection;
import cafe.Model.Utility.DBConnectionPool;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

public class CustomerModel extends AbstractModel {

    private static CustomerModel instance = null;

    private DBConnectionPool connectionPool = null;

    /**
     * Get all table and it's informations
     *
     * @return ArrayList<Table> all table in database
     */
    private CustomerModel() {
        this.connectionPool = DBConnectionPool.getInstance();
    }

    public static synchronized CustomerModel getInstance() {
        if (instance == null) {
            instance = new CustomerModel();
        }

        return instance;
    }

    @Override
    public List<? extends DataObject> getAll() {

        List<Customer> result = new LinkedList();

        //connect
        DBConnection connectionObject = null;
        try {
            connectionObject = this.connectionPool.getConnection();

        } catch (Exception ex) {

        }

        //NOTE: we must use ` ` to cover table name 
        String query = "SELECT * FROM `customer` ";

        ResultSet rs = null;
        try {
            rs = connectionObject.query(query);

            while (rs.next()) {
                Customer customer = new Customer();
                customer.setId(rs.getString("id"));
                customer.setName(rs.getString("name"));
                customer.setSex(rs.getBoolean("sex"));
                customer.setPhone(rs.getString("phone"));

                result.add(customer);
            }

        } catch (SQLException ex) {

        } finally {
            this.connectionPool.giveBack(connectionObject);

            try {
                if (null != rs) {
                    rs.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(CustomerModel.class.getName()).log(Level.SEVERE, null, ex);
            }
            return result;
        }
    }

    @Override
    public Customer getByID(String id) {
        Customer customer = null;

        //connect
        DBConnection connectionObject = null;
        try {
            connectionObject = this.connectionPool.getConnection();

        } catch (Exception ex) {

        }

        //NOTE: we must use ` ` to cover table name 
        String query = "SELECT * FROM `customer` WHERE `id` = ? LIMIT 1;";

        ResultSet rs = null;
        try {
            rs = connectionObject.query(query, id);

            while (rs.next()) {
                customer = new Customer();
                customer.setId(rs.getString("id"));
                customer.setName(rs.getString("name"));
                customer.setSex(rs.getBoolean("sex"));
                customer.setPhone(rs.getString("phone"));

            }

        } catch (SQLException ex) {

        } finally {
            this.connectionPool.giveBack(connectionObject);

            try {
                if (null != rs) {
                    rs.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(CustomerModel.class.getName()).log(Level.SEVERE, null, ex);
            }

            return customer;
        }
    }

    @Override
    public int insert(DataObject record) {
        Customer customer = (Customer) record;

        //connect
        DBConnection connectionObject = null;
        try {
            connectionObject = this.connectionPool.getConnection();

        } catch (Exception ex) {

        }

        //NOTE: we must use ` ` to cover table name 
       
        String query = "INSERT INTO `customer`(`name`, `sex`, `phone`) " +
                       "VALUES (?,?,?)";

        int result = 0;

        try {
            connectionObject.excute(query, customer.toObjectArray());

        } catch (SQLException ex) {

        } finally {
            this.connectionPool.giveBack(connectionObject);

            return result;
        }
    }

    @Override
    public int edit(String fromRecordId, DataObject toRecord) {
        Customer customer = (Customer) toRecord;

        //connect
        DBConnection connectionObject = null;
        try {
            connectionObject = this.connectionPool.getConnection();

        } catch (Exception ex) {

        }

        //NOTE: we must use ` ` to cover table name 
        String query = "UPDATE `customer` "
                + "SET `name`=?,`sex`=?,`phone`=? "
                + "WHERE `id` = ?";

        int result = 0;

        try {
            result = connectionObject.excute(query, customer.getName(), customer.getSex(), customer.getPhone(),
                    fromRecordId);

        } catch (SQLException ex) {

        } finally {
            this.connectionPool.giveBack(connectionObject);

            return result;
        }
    }

    @Override
    public int delete(String id) {
        //connect
        DBConnection connectionObject = null;
        try {
            connectionObject = this.connectionPool.getConnection();

        } catch (Exception ex) {

        }

        //NOTE: we must use ` ` to cover table name 
        String query = "DELETE FROM `customer` WHERE `id` = ?;";

        int result = 0;

        try {
            result = connectionObject.excute(query, id);

        } catch (SQLException ex) {

        } finally {
            this.connectionPool.giveBack(connectionObject);

            return result;
        }
    }

}
